﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class PositionCommandDtlsDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PositionCommandDtlsDAL()
        {

        }
        public DataSet LoadPositionCommandDtls(Nullable<int> Sequence, Nullable<int> CommandSequence)
        {
            DataSet ds = null;

            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@CommandSequence", CommandSequence);

                strSQL = "SELECT d.CMD_POS_DTLS_SEQ ,d.CMD_POS_SEQ, d.POS_SEQ, d.OLD_POS_REF, d.NEW_POS_REF"
                         + ", d.OLD_POS_NAME, d.NEW_POS_NAME, d.OLD_JOB_SITE_SEQ, d.NEW_JOB_SITE_SEQ"
                         + ", d.OLD_JOB_SITE_NAME, d.NEW_JOB_SITE_NAME, d.OLD_POS_TYPE_SEQ, d.NEW_POS_TYPE_SEQ"
                         + ", d.OLD_FIELD_SEQ, d.NEW_FIELD_SEQ, d.OLD_EXEC_SEQ, d.NEW_EXEC_SEQ"
                         + ", d.OLD_CLASS_SPEC_SEQ, d.NEW_CLASS_SPEC_SEQ, d.OLD_SALARY_QUOTA, d.NEW_SALARY_QUOTA"
                         + ", d.START_DATE, d.REMARKS"
                         + ", of.FIELD_POS_NAME AS OLD_FIELD_NAME, nf.FIELD_POS_NAME AS NEW_FIELD_NAME"
                         + ", oe.EXEC_POS_NAME AS OLD_EXEC_NAME, ne.EXEC_POS_NAME AS NEW_EXEC_NAME"
                         + ", opt.POS_TYPE_NAME AS OLD_POS_TYPE_NAME, npt.POS_TYPE_NAME AS NEW_POS_TYPE_NAME"
                         + ", os.CLASS_SPEC_NAME AS OLD_CLASS_SPEC_NAME, ns.CLASS_SPEC_NAME AS NEW_CLASS_SPEC_NAME"
                         + ", rs.RECORD_STATUS_NAME AS CMD_STATUS_NAME"
                         + ", c.CMD_POS_REF, c.CMD_POS_DESCR, c.APPROVED_BY"
                         + ", c.APPROVED_NAME, c.APPROVED_POSITION, c.APPROVED_DATE, c.CMD_STATUS"
                     + " FROM HRM_MP_POSITION_COMMAND_DTLS d"
                         + " LEFT JOIN HRM_MP_POSITION_COMMANDS c ON d.CMD_POS_SEQ = c.CMD_POS_SEQ"
                         + " LEFT JOIN MAS_RECORD_STATUSES rs ON c.CMD_STATUS = rs.RECORD_STATUS"
                         + " LEFT JOIN HRM_MP_JOB_SITES oj ON d.OLD_JOB_SITE_SEQ = oj.JOB_SITE_SEQ"
                         + " LEFT JOIN HRM_MP_JOB_SITES nj ON d.NEW_JOB_SITE_SEQ = nj.JOB_SITE_SEQ"
                         + " LEFT JOIN HRM_MP_FIELD_POSITIONS of ON d.OLD_FIELD_SEQ = of.FIELD_POS_SEQ"
                         + " LEFT JOIN HRM_MP_FIELD_POSITIONS nf ON d.NEW_FIELD_SEQ = nf.FIELD_POS_SEQ"
                         + " LEFT JOIN HRM_MP_EXEC_POSITIONS oe ON d.OLD_EXEC_SEQ = oe.EXEC_POS_SEQ"
                         + " LEFT JOIN HRM_MP_EXEC_POSITIONS ne ON d.NEW_EXEC_SEQ = ne.EXEC_POS_SEQ"
                         + " LEFT JOIN HRM_MP_POSITION_TYPES opt ON d.OLD_POS_TYPE_SEQ = opt.POS_TYPE_SEQ"
                         + " LEFT JOIN HRM_MP_POSITION_TYPES npt ON d.NEW_POS_TYPE_SEQ = npt.POS_TYPE_SEQ"
                         + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS os ON d.OLD_CLASS_SPEC_SEQ = os.CLASS_SPEC_SEQ"
                         + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS ns ON d.NEW_CLASS_SPEC_SEQ = ns.CLASS_SPEC_SEQ"
                     + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                     + (Sequence.HasValue ? " AND d.CMD_POS_DTLS_SEQ = @Sequence" : "")
                     + (CommandSequence.HasValue ? " AND d.CMD_POS_SEQ = @CommandSequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                
                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

        }
        public PositionCommandDtlsInfo GetPositionCommandDtlsInfo(Nullable<int> Seq)
        {
            PositionCommandDtlsInfo Info = new PositionCommandDtlsInfo();

            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Seq", Seq);

                strSQL = "SELECT d.CMD_POS_DTLS_SEQ ,d.CMD_POS_SEQ, d.POS_SEQ, d.OLD_POS_REF, d.NEW_POS_REF"
                         + ", d.OLD_POS_NAME, d.NEW_POS_NAME, d.OLD_JOB_SITE_SEQ, d.NEW_JOB_SITE_SEQ"
                         + ", d.OLD_JOB_SITE_NAME, d.NEW_JOB_SITE_NAME, d.OLD_POS_TYPE_SEQ, d.NEW_POS_TYPE_SEQ"
                         + ", d.OLD_FIELD_SEQ, d.NEW_FIELD_SEQ, d.OLD_EXEC_SEQ, d.NEW_EXEC_SEQ"
                         + ", d.OLD_CLASS_SPEC_SEQ, d.NEW_CLASS_SPEC_SEQ, d.OLD_SALARY_QUOTA, d.NEW_SALARY_QUOTA"
                         + ", d.START_DATE, d.REMARKS"
                         + ", of.FIELD_POS_NAME AS OLD_FIELD_NAME, nf.FIELD_POS_NAME AS NEW_FIELD_NAME"
                         + ", oe.EXEC_POS_NAME AS OLD_EXEC_NAME, ne.EXEC_POS_NAME AS NEW_EXEC_NAME"
                         + ", opt.POS_TYPE_NAME AS OLD_POS_TYPE_NAME, npt.POS_TYPE_NAME AS NEW_POS_TYPE_NAME"
                         + ", os.CLASS_SPEC_NAME AS OLD_CLASS_SPEC_NAME, ns.CLASS_SPEC_NAME AS NEW_CLASS_SPEC_NAME"
                         + ", rs.RECORD_STATUS_NAME AS CMD_STATUS_NAME"
                         + ", c.CMD_POS_REF, c.CMD_POS_DESCR, c.APPROVED_BY"
                         + ", c.APPROVED_NAME, c.APPROVED_POSITION, c.APPROVED_DATE, c.CMD_STATUS"
                     + " FROM HRM_MP_POSITION_COMMAND_DTLS d"
                         + " LEFT JOIN HRM_MP_POSITION_COMMANDS c ON d.CMD_POS_SEQ = c.CMD_POS_SEQ"
                         + " LEFT JOIN MAS_RECORD_STATUSES rs ON c.CMD_STATUS = rs.RECORD_STATUS"
                         + " LEFT JOIN HRM_MP_JOB_SITES oj ON d.OLD_JOB_SITE_SEQ = oj.JOB_SITE_SEQ"
                         + " LEFT JOIN HRM_MP_JOB_SITES nj ON d.NEW_JOB_SITE_SEQ = nj.JOB_SITE_SEQ"
                         + " LEFT JOIN HRM_MP_FIELD_POSITIONS of ON d.OLD_FIELD_SEQ = of.FIELD_POS_SEQ"
                         + " LEFT JOIN HRM_MP_FIELD_POSITIONS nf ON d.NEW_FIELD_SEQ = nf.FIELD_POS_SEQ"
                         + " LEFT JOIN HRM_MP_EXEC_POSITIONS oe ON d.OLD_EXEC_SEQ = oe.EXEC_POS_SEQ"
                         + " LEFT JOIN HRM_MP_EXEC_POSITIONS ne ON d.NEW_EXEC_SEQ = ne.EXEC_POS_SEQ"
                         + " LEFT JOIN HRM_MP_POSITION_TYPES opt ON d.OLD_POS_TYPE_SEQ = opt.POS_TYPE_SEQ"
                         + " LEFT JOIN HRM_MP_POSITION_TYPES npt ON d.NEW_POS_TYPE_SEQ = npt.POS_TYPE_SEQ"
                         + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS os ON d.OLD_CLASS_SPEC_SEQ = os.CLASS_SPEC_SEQ"
                         + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS ns ON d.NEW_CLASS_SPEC_SEQ = ns.CLASS_SPEC_SEQ"
                     + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                        + " AND d.CMD_POS_DTLS_SEQ = @Seq";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    Info.Seq = rdr.IsDBNull(0) ? (int?)null : rdr.GetInt32(0);
                    Info.CmdPosSeq = rdr.IsDBNull(1) ? (int?)null : rdr.GetInt32(1);
                    Info.PosSeq = rdr.IsDBNull(2) ? (int?)null : rdr.GetInt32(2);
                    Info.OldPosRef = rdr.IsDBNull(3) ? "" : rdr.GetString(3);
                    Info.NewPosRef = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
                    Info.OldPosName = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
                    Info.NewPosName = rdr.IsDBNull(6) ? "" : rdr.GetString(6);
                    Info.OldJobSiteSeq = rdr.IsDBNull(7) ? (int?)null : rdr.GetInt32(7);
                    Info.NewJobSiteSeq = rdr.IsDBNull(8) ? (int?)null : rdr.GetInt32(8);
                    Info.OldJobSiteName = rdr.IsDBNull(9) ? "" : rdr.GetString(9);
                    Info.NewJobSiteName = rdr.IsDBNull(10) ? "" : rdr.GetString(10);
                    Info.OldPosTypeSeq = rdr.IsDBNull(11) ? (int?)null : rdr.GetInt32(11);
                    Info.NewPosTypeSeq = rdr.IsDBNull(12) ? (int?)null : rdr.GetInt32(12);
                    Info.OldFieldSeq = rdr.IsDBNull(13) ? (int?)null : rdr.GetInt32(13);
                    Info.NewFieldSeq = rdr.IsDBNull(14) ? (int?)null : rdr.GetInt32(14);
                    Info.OldExecSeq = rdr.IsDBNull(15) ? (int?)null : rdr.GetInt32(15);
                    Info.NewExecSeq = rdr.IsDBNull(16) ? (int?)null : rdr.GetInt32(16);
                    Info.OldClassSpecSeq = rdr.IsDBNull(17) ? (int?)null : rdr.GetInt32(17);
                    Info.NewClassSpecSeq = rdr.IsDBNull(18) ? (int?)null : rdr.GetInt32(18);
                    Info.OldSalaryQuota = rdr.IsDBNull(19) ? (double?)null : rdr.GetDouble(19);
                    Info.NewSalaryQuota = rdr.IsDBNull(20) ? (double?)null : rdr.GetDouble(20);
                    Info.StartDate = rdr.IsDBNull(21) ? (DateTime?)null : rdr.GetDateTime(21);
                    Info.Remarks = rdr.IsDBNull(22) ? "" : rdr.GetString(22);
                }
                return Info;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
                        
        }
        public IList<PositionCommandDtlsInfo> LoadGridPositionCommandDtls(Nullable<int> CmdSeq)
        {
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PositionCommandDtlsInfo> infoList = new List<PositionCommandDtlsInfo>();

            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@CmdSeq", CmdSeq);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT d.CMD_POS_DTLS_SEQ ,d.CMD_POS_SEQ, d.POS_SEQ, d.OLD_POS_REF, d.NEW_POS_REF"
                         + ", d.OLD_POS_NAME, d.NEW_POS_NAME, d.OLD_JOB_SITE_SEQ, d.NEW_JOB_SITE_SEQ"
                         + ", d.OLD_JOB_SITE_NAME, d.NEW_JOB_SITE_NAME, d.OLD_POS_TYPE_SEQ, d.NEW_POS_TYPE_SEQ"
                         + ", d.OLD_FIELD_SEQ, d.NEW_FIELD_SEQ, d.OLD_EXEC_SEQ, d.NEW_EXEC_SEQ"
                         + ", d.OLD_CLASS_SPEC_SEQ, d.NEW_CLASS_SPEC_SEQ, d.OLD_SALARY_QUOTA, d.NEW_SALARY_QUOTA"
                         + ", d.START_DATE, d.REMARKS"
                         + ", oldf.FIELD_POS_NAME AS OLD_FIELD_NAME, nf.FIELD_POS_NAME AS NEW_FIELD_NAME"
                         + ", oe.EXEC_POS_NAME AS OLD_EXEC_NAME, ne.EXEC_POS_NAME AS NEW_EXEC_NAME"
                         + ", opt.POS_TYPE_NAME AS OLD_POS_TYPE_NAME, npt.POS_TYPE_NAME AS NEW_POS_TYPE_NAME"
                         + ", os.CLASS_SPEC_NAME AS OLD_CLASS_SPEC_NAME, ns.CLASS_SPEC_NAME AS NEW_CLASS_SPEC_NAME"
                         + ", rs.RECORD_STATUS_NAME AS CMD_STATUS_NAME"
                         + ", c.CMD_POS_REF, c.CMD_POS_DESCR, c.APPROVED_BY"
                         + ", c.APPROVED_NAME, c.APPROVED_POSITION, c.APPROVED_DATE, c.CMD_STATUS"
                    + " FROM HRM_MP_POSITION_COMMAND_DTLS d"
                        + " LEFT JOIN HRM_MP_POSITION_COMMANDS c ON d.CMD_POS_SEQ = c.CMD_POS_SEQ"
                        + " LEFT JOIN MAS_RECORD_STATUSES rs ON c.CMD_STATUS = rs.RECORD_STATUS"
                        + " LEFT JOIN HRM_MP_JOB_SITES oj ON d.OLD_JOB_SITE_SEQ = oj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES nj ON d.NEW_JOB_SITE_SEQ = nj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS oldf ON d.OLD_FIELD_SEQ = oldf.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS nf ON d.NEW_FIELD_SEQ = nf.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_EXEC_POSITIONS oe ON d.OLD_EXEC_SEQ = oe.EXEC_POS_SEQ"
                        + " LEFT JOIN HRM_MP_EXEC_POSITIONS ne ON d.NEW_EXEC_SEQ = ne.EXEC_POS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITION_TYPES opt ON d.OLD_POS_TYPE_SEQ = opt.POS_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_POSITION_TYPES npt ON d.NEW_POS_TYPE_SEQ = npt.POS_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS os ON d.OLD_CLASS_SPEC_SEQ = os.CLASS_SPEC_SEQ"
                        + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS ns ON d.NEW_CLASS_SPEC_SEQ = ns.CLASS_SPEC_SEQ"
                    + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                        + " AND d.CMD_POS_SEQ = @CmdSeq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PositionCommandDtlsInfo Info = new PositionCommandDtlsInfo();
                        Info.Seq = Convert.IsDBNull(dr[i]["CMD_POS_DTLS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["CMD_POS_DTLS_SEQ"].ToString());
                        Info.CmdPosSeq = Convert.IsDBNull(dr[i]["CMD_POS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["CMD_POS_SEQ"].ToString());
                        Info.CmdPosRef = Convert.IsDBNull(dr[i]["CMD_POS_REF"]) ? "" : (dr[i]["CMD_POS_REF"]).ToString();
                        Info.CmdPosDescr = Convert.IsDBNull(dr[i]["CMD_POS_DESCR"]) ? "" : (dr[i]["CMD_POS_DESCR"]).ToString();
                        Info.PosSeq = Convert.IsDBNull(dr[i]["POS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["POS_SEQ"].ToString());
                        Info.OldPosRef = Convert.IsDBNull(dr[i]["OLD_POS_REF"]) ? "" : (dr[i]["OLD_POS_REF"]).ToString();
                        Info.NewPosRef = Convert.IsDBNull(dr[i]["NEW_POS_REF"]) ? "" : (dr[i]["NEW_POS_REF"]).ToString();
                        Info.OldPosName = Convert.IsDBNull(dr[i]["OLD_POS_NAME"]) ? "" : (dr[i]["OLD_POS_NAME"]).ToString();
                        Info.NewPosName = Convert.IsDBNull(dr[i]["NEW_POS_NAME"]) ? "" : (dr[i]["NEW_POS_NAME"]).ToString();
                        Info.OldJobSiteSeq = Convert.IsDBNull(dr[i]["OLD_JOB_SITE_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["OLD_JOB_SITE_SEQ"].ToString());
                        Info.NewJobSiteSeq = Convert.IsDBNull(dr[i]["NEW_JOB_SITE_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["NEW_JOB_SITE_SEQ"].ToString());
                        Info.OldJobSiteName = Convert.IsDBNull(dr[i]["OLD_JOB_SITE_NAME"]) ? "" : (dr[i]["OLD_JOB_SITE_NAME"]).ToString();
                        Info.NewJobSiteName = Convert.IsDBNull(dr[i]["NEW_JOB_SITE_NAME"]) ? "" : (dr[i]["NEW_JOB_SITE_NAME"]).ToString();
                        Info.OldPosTypeSeq = Convert.IsDBNull(dr[i]["OLD_POS_TYPE_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["OLD_POS_TYPE_SEQ"].ToString());
                        Info.NewPosTypeSeq = Convert.IsDBNull(dr[i]["NEW_POS_TYPE_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["NEW_POS_TYPE_SEQ"].ToString());

                        Info.OldPosTypeName = Convert.IsDBNull(dr[i]["OLD_POS_TYPE_NAME"]) ? "" : (dr[i]["OLD_POS_TYPE_NAME"]).ToString();
                        Info.NewPosTypeName = Convert.IsDBNull(dr[i]["NEW_POS_TYPE_NAME"]) ? "" : (dr[i]["NEW_POS_TYPE_NAME"]).ToString();

                        Info.OldFieldSeq = Convert.IsDBNull(dr[i]["OLD_FIELD_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["OLD_FIELD_SEQ"].ToString());
                        Info.NewFieldSeq = Convert.IsDBNull(dr[i]["NEW_FIELD_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["NEW_FIELD_SEQ"].ToString());

                        Info.OldFieldName = Convert.IsDBNull(dr[i]["OLD_FIELD_NAME"]) ? "" : (dr[i]["OLD_FIELD_NAME"]).ToString();
                        Info.NewFieldName = Convert.IsDBNull(dr[i]["NEW_FIELD_NAME"]) ? "" : (dr[i]["NEW_FIELD_NAME"]).ToString();

                        Info.OldExecSeq = Convert.IsDBNull(dr[i]["OLD_EXEC_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["OLD_EXEC_SEQ"].ToString());
                        Info.NewExecSeq = Convert.IsDBNull(dr[i]["NEW_EXEC_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["NEW_EXEC_SEQ"].ToString());

                        Info.OldExecName = Convert.IsDBNull(dr[i]["OLD_EXEC_NAME"]) ? "" : (dr[i]["OLD_EXEC_NAME"]).ToString();
                        Info.NewExecName = Convert.IsDBNull(dr[i]["NEW_EXEC_NAME"]) ? "" : (dr[i]["NEW_EXEC_NAME"]).ToString();

                        Info.OldClassSpecSeq = Convert.IsDBNull(dr[i]["OLD_CLASS_SPEC_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["OLD_CLASS_SPEC_SEQ"].ToString());
                        Info.NewClassSpecSeq = Convert.IsDBNull(dr[i]["NEW_CLASS_SPEC_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["NEW_CLASS_SPEC_SEQ"].ToString());
                        Info.OldSalaryQuota = Convert.IsDBNull(dr[i]["OLD_SALARY_QUOTA"]) ? (double?)null : double.Parse(dr[i]["OLD_SALARY_QUOTA"].ToString());
                        Info.NewSalaryQuota = Convert.IsDBNull(dr[i]["NEW_SALARY_QUOTA"]) ? (double?)null : double.Parse(dr[i]["NEW_SALARY_QUOTA"].ToString());
                        Info.StartDate = Convert.IsDBNull(dr[i]["START_DATE"]) ? (DateTime?)null : DateTime.Parse(dr[i]["START_DATE"].ToString());
                        Info.Remarks = Convert.IsDBNull(dr[i]["REMARKS"]) ? "" : (dr[i]["REMARKS"]).ToString();

                        infoList.Add(Info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }

        public void Insert(PositionCommandDtlsInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(25);

                dbManager.AddParameters(0, "@CmdPosSeq", Info.CmdPosSeq);
                dbManager.AddParameters(1, "@PosSeq", Info.PosSeq);
                dbManager.AddParameters(2, "@OldPosRef", Info.OldPosRef);
                dbManager.AddParameters(3, "@NewPosRef", Info.NewPosRef);
                dbManager.AddParameters(4, "@OldPosName", Info.OldPosName);
                dbManager.AddParameters(5, "@NewPosName", Info.NewPosName);
                dbManager.AddParameters(6, "@OldJobSiteSeq", Info.OldJobSiteSeq);
                dbManager.AddParameters(7, "@NewJobSiteSeq", Info.NewJobSiteSeq);
                dbManager.AddParameters(8, "@OldJobSiteName", Info.OldJobSiteName);
                dbManager.AddParameters(9, "@NewJobSiteName", Info.NewJobSiteName);
                dbManager.AddParameters(10, "@OldFiledSeq", Info.OldFieldSeq);
                dbManager.AddParameters(11, "@NewFieldSeq", Info.NewFieldSeq);
                dbManager.AddParameters(12, "@OldExecSeq", Info.OldExecSeq);
                dbManager.AddParameters(13, "@NewExecSeq", Info.NewExecSeq);
                dbManager.AddParameters(14, "@OldPosType", Info.OldPosTypeSeq);
                dbManager.AddParameters(15, "@NewPosType", Info.NewPosTypeSeq);
                dbManager.AddParameters(16, "@OldClassSpec", Info.OldClassSpecSeq);
                dbManager.AddParameters(17, "@NewClassSpec", Info.NewClassSpecSeq);
                dbManager.AddParameters(18, "@OldSalary", Info.OldSalaryQuota);
                dbManager.AddParameters(19, "@NewSalary", Info.NewSalaryQuota);
                dbManager.AddParameters(20, "@StartDate", Info.StartDate);
                dbManager.AddParameters(21, "@Remarks", Info.Remarks);
                dbManager.AddParameters(22, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(23, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(24, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_MP_POSITION_COMMAND_DTLS(CMD_POS_SEQ, POS_SEQ, OLD_POS_REF, NEW_POS_REF"
                        + ", OLD_POS_NAME, NEW_POS_NAME, OLD_JOB_SITE_SEQ, NEW_JOB_SITE_SEQ"
                        + ", OLD_JOB_SITE_NAME, NEW_JOB_SITE_NAME, OLD_FIELD_SEQ, NEW_FIELD_SEQ"
                        + ", OLD_EXEC_SEQ, NEW_EXEC_SEQ, OLD_POS_TYPE_SEQ, NEW_POS_TYPE_SEQ"
                        + ", OLD_CLASS_SPEC_SEQ, NEW_CLASS_SPEC_SEQ, OLD_SALARY_QUOTA, NEW_SALARY_QUOTA"
                        + ", START_DATE, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@CmdPosSeq, @PosSeq, @OldPosRef, @NewPosRef, @OldPosName, @NewPosName"
                        + ", @OldJobSiteSeq, @NewJobSiteSeq, @OldJobSiteName, @NewJobSiteName"
                        + ", @OldFiledSeq, @NewFieldSeq, @OldExecSeq, @NewExecSeq"
                        + ", @OldPosType, @NewPosType, @OldClassSpec, @NewClassSpec"
                        + ", @OldSalary, @NewSalary, @StartDate, @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(PositionCommandDtlsInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(25);

                dbManager.AddParameters(0, "@CmdPosSeq", Info.CmdPosSeq);
                dbManager.AddParameters(1, "@PosSeq", Info.PosSeq);
                dbManager.AddParameters(2, "@OldPosRef", Info.OldPosRef);
                dbManager.AddParameters(3, "@NewPosRef", Info.NewPosRef);
                dbManager.AddParameters(4, "@OldPosName", Info.OldPosName);
                dbManager.AddParameters(5, "@NewPosName", Info.NewPosName);
                dbManager.AddParameters(6, "@OldJobSiteSeq", Info.OldJobSiteSeq);
                dbManager.AddParameters(7, "@NewJobSiteSeq", Info.NewJobSiteSeq);
                dbManager.AddParameters(8, "@OldJobSiteName", Info.OldJobSiteName);
                dbManager.AddParameters(9, "@NewJobSiteName", Info.NewJobSiteName);
                dbManager.AddParameters(10, "@OldFiledSeq", Info.OldFieldSeq);
                dbManager.AddParameters(11, "@NewFieldSeq", Info.NewFieldSeq);
                dbManager.AddParameters(12, "@OldExecSeq", Info.OldExecSeq);
                dbManager.AddParameters(13, "@NewExecSeq", Info.NewExecSeq);
                dbManager.AddParameters(14, "@OldPosType", Info.OldPosTypeSeq);
                dbManager.AddParameters(15, "@NewPosType", Info.NewPosTypeSeq);
                dbManager.AddParameters(16, "@OldClassSpec", Info.OldClassSpecSeq);
                dbManager.AddParameters(17, "@NewClassSpec", Info.NewClassSpecSeq);
                dbManager.AddParameters(18, "@OldSalary", Info.OldSalaryQuota);
                dbManager.AddParameters(19, "@NewSalary", Info.NewSalaryQuota);
                dbManager.AddParameters(20, "@StartDate", Info.StartDate);
                dbManager.AddParameters(21, "@Remarks", Info.Remarks);
                dbManager.AddParameters(22, "@Seq", Info.Seq);
                dbManager.AddParameters(23, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(24, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_POSITION_COMMAND_DTLS SET"
                        + " CMD_POS_SEQ = @CmdPosSeq"
                        + ", POS_SEQ = @PosSeq"
                        + ", OLD_POS_REF = @OldPosRef"
                        + ", NEW_POS_REF = @NewPosRef"
                        + ", OLD_POS_NAME = @OldPosName"
                        + ", NEW_POS_NAME = @NewPosName"
                        + ", OLD_JOB_SITE_SEQ = @OldJobSiteSeq"
                        + ", NEW_JOB_SITE_SEQ = @NewJobSiteSeq"
                        + ", OLD_JOB_SITE_NAME = @OldJobSiteName"
                        + ", NEW_JOB_SITE_NAME = @NewJobSiteName"
                        + ", OLD_FIELD_SEQ = @OldFiledSeq"
                        + ", NEW_FIELD_SEQ = @NewFieldSeq"
                        + ", OLD_EXEC_SEQ = @OldExecSeq"
                        + ", NEW_EXEC_SEQ = @NewExecSeq"
                        + ", OLD_POS_TYPE_SEQ = @OldPosType"
                        + ", NEW_POS_TYPE_SEQ = @NewPosType"
                        + ", OLD_CLASS_SPEC_SEQ = @OldClassSpec"
                        + ", NEW_CLASS_SPEC_SEQ = @NewClassSpec"
                        + ", OLD_SALARY_QUOTA = @OldSalary"
                        + ", NEW_SALARY_QUOTA = @NewSalary"
                        + ", START_DATE = @StartDate"
                        + ", REMARKS = @Remarks"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CMD_POS_DTLS_SEQ = @Seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(PositionCommandDtlsInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Seq", Info.Seq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_POSITION_COMMAND_DTLS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CMD_POS_DTLS_SEQ = @Seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
